﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class D_Phong:AccessData
    {
        //-----Hàm lấy ra hết------        
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Phong_Get_List";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm lấy theo PK PhongID------
        public M_Phong GetbyPK_PhongID(int PhongID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Phong_GetByPhongId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@PhongID", PhongID);
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                M_Phong M_Phong = null;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        M_Phong = new M_Phong();

                        M_Phong.PhongID = Convert.ToInt32(dr["PhongID"]);
                        if (dr["Giatien"] != DBNull.Value)
                            M_Phong.Giatien = Convert.ToDecimal(dr["Giatien"]);
                        if (dr["Mota"] != DBNull.Value)
                            M_Phong.Mota = Convert.ToString(dr["Mota"]);
                        if (dr["Sophong"] != DBNull.Value)
                            M_Phong.Sophong = Convert.ToString(dr["Sophong"]);
                        if (dr["Trangthai"] != DBNull.Value)
                            M_Phong.Trangthai = Convert.ToString(dr["Trangthai"]);                        
                    }
                }
                dr.Close();
                sqlCmd.Connection.Close();
                return M_Phong;
            }
        }        

        //-----Hàm Insert------
        public void Insert(M_Phong M_Phong)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Phong_Insert";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@PhongID", SqlDbType.Int)).Value = M_Phong.PhongID;
            sqlCmd.Parameters.Add(new SqlParameter("@Hovaten", SqlDbType.Decimal)).Value = M_Phong.Giatien;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaysinh", SqlDbType.NText)).Value = M_Phong.Mota;
            sqlCmd.Parameters.Add(new SqlParameter("@SoCMND", SqlDbType.NVarChar, 50)).Value = M_Phong.Sophong;
            sqlCmd.Parameters.Add(new SqlParameter("@Gioitinh", SqlDbType.NVarChar, 50)).Value = M_Phong.Trangthai;            

            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
        }

        //-----Hàm Update------
        public bool Update(M_Phong M_Phong)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Phong_Update";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@PhongID", SqlDbType.Int)).Value = M_Phong.PhongID;
            sqlCmd.Parameters.Add(new SqlParameter("@Hovaten", SqlDbType.Decimal)).Value = M_Phong.Giatien;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaysinh", SqlDbType.NText)).Value = M_Phong.Mota;
            sqlCmd.Parameters.Add(new SqlParameter("@SoCMND", SqlDbType.NVarChar, 50)).Value = M_Phong.Sophong;
            sqlCmd.Parameters.Add(new SqlParameter("@Gioitinh", SqlDbType.NVarChar, 50)).Value = M_Phong.Trangthai;

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }

        //-----Hàm xóa------
        public bool Delete(int PhongID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Phong_Delete";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@PhongID", PhongID));

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }
    }
}
